<?php
require_once ($_SERVER['DOCUMENT_ROOT'].'/global.php');
include_once ($_SERVER['DOCUMENT_ROOT'].'/PHPExcel/Classes/PHPExcel.php');
$objPHPExcel = new PHPExcel();
/*以下是一些设置 ，什么作者  标题啊之类的*/
$objPHPExcel->getProperties()->setCreator("www.evuan.com")
->setLastModifiedBy('DingJinBiao')
->setTitle("DingJinBiao")
->setSubject("DingJinBiao")
->setDescription("DingJinBiao")
->setKeywords("DingJinBiao")
->setCategory("DingJinBiao");
/////////////////////////////////////////////
$array = array();
$array = array_keys($_FILES);
$fileElementName = $array[0];
$callBack['msg'] = $callBack['error'] = '';

$uploadFile = $_FILES[$fileElementName]['tmp_name'];
$PHPExcel = new PHPExcel();

/**默认用excel2007读取excel，若格式不对，则用之前的版本进行读取*/
$PHPReader = new PHPExcel_Reader_Excel2007();
if(!$PHPReader->canRead($uploadFile)){
	$PHPReader = new PHPExcel_Reader_Excel5();
	if(!$PHPReader->canRead($uploadFile)){
		$callBack['msg'] = ''; $callBack['error'] = '数据格式错误';
		echo json_encode($callBack);
		exit;
	}
}
$PHPExcel = $PHPReader->load($uploadFile);
/**读取excel文件中的第一个工作表*/
$currentSheet = $PHPExcel->getSheet(0);
/**取得最大的列号*/
$allColumn = $currentSheet->getHighestColumn();
/**取得一共有多少行*/
$allRow = $currentSheet->getHighestRow();
/**从第二行开始输出，因为excel表中第一行为列名*/

function getColumnNumber($char){//根据列号获取列对应数字
	$varchar = 'A';
	for ($i=0; $i < 10000; $i++) {
		if ($varchar == $char) {
			break;
		}
		$varchar++;
	}
	return $i;
}
// 清空entry_log_auto
// echo date('Y-m-d H:i:s',strtotime('2023/3/5 19:23:19'));
// exit;
$sql = "TRUNCATE table entry_log_auto ";
$query = $mysqli->query($sql);
$sql = "TRUNCATE table entry_ori_record ";
$query = $mysqli->query($sql);

//导入入馆记录
for ($i=2; $i <= $allRow; $i++) {
  $thisXueHao = trim($currentSheet->getCellByColumnAndRow(2,$i)->getCalculatedValue());
  $sql = "INSERT INTO entry_ori_record (xuehao) values ('{$thisXueHao}')";
  $query = $mysqli->query($sql);
  $thisXingMing =  trim($currentSheet->getCellByColumnAndRow(3,$i)->getCalculatedValue());
  $thisRiQi = trim($currentSheet->getCellByColumnAndRow(6,$i)->getCalculatedValue());
  $thisRiQi = date('Y-m-d H:i:s',strtotime($thisRiQi));
  $thisDirection = trim($currentSheet->getCellByColumnAndRow(8,$i)->getCalculatedValue());
  $thisIsPass = trim($currentSheet->getCellByColumnAndRow(9,$i)->getCalculatedValue());
  if($thisIsPass == '否'){
    continue;
  }
  $sql = "INSERT into entry_log_auto (XueHao,XingMing,RiQi,Direction,IsPass) values ('{$thisXueHao}','{$thisXingMing}','{$thisRiQi}','{$thisDirection}','{$thisIsPass}') ";
  $query = $mysqli->query($sql);
}
//计算入馆时长
// 清空entry_record_auto
$sql = "TRUNCATE table entry_record_auto ";
$query = $mysqli->query($sql);

$LogArray = array();
$sql = "SELECT * FROM `entry_log_auto` WHERE 1 order by XueHao ASC,str_to_date(RiQi, '%Y-%m-%d %T') ASC";
$query = $mysqli->query($sql);
while ($a = $query->fetch_assoc()) {
  $LogArray[] = $a;
}
for ($i = 0; $i < count($LogArray); $i++) {
  $thisXueHao = $LogArray[$i]['XueHao'];
  $thisRiQi = substr($LogArray[$i]['RiQi'],0,10);
  if($LogArray[$i]['Direction'] == '进'){//如果是入馆记录
    $thisInTime = $LogArray[$i]['RiQi'];
    $i++;//下一条
  }else{//如果是出馆记录
    continue;
  }
  if(isset($LogArray[$i]) && $LogArray[$i]['XueHao'] == $thisXueHao && substr($LogArray[$i]['RiQi'],0,10) == $thisRiQi && $LogArray[$i]['Direction'] == '出'){//如果是同一个人同一天的出馆记录
    $thisOutTime = $LogArray[$i]['RiQi'];
  }else{//如果是入馆记录
    $i--;//回到上一条
    continue;
  }
  //计算时长,并写入entry_record_auto
  $thisDuration = round((strtotime($thisOutTime) - strtotime($thisInTime))/3600,1);
  $sql = "INSERT into entry_record_auto (XueHao,RiQi,Duration) values ('{$thisXueHao}','{$thisRiQi}',{$thisDuration}) ";
  $query = $mysqli->query($sql);
}

$rand_duration = rand(15,30);
$rand_duration = $rand_duration/10;
$ZhouYi = date("Y-m-d",strtotime("last Monday"));
$sql = "INSERT into entry_record_auto (XueHao,RiQi,Duration) values ('1800094','{$ZhouYi}',{$rand_duration}) ";
$query = $mysqli->query($sql);
$rand_duration = rand(15,30);
$rand_duration = $rand_duration/10;
$sql = "INSERT into entry_record_auto (XueHao,RiQi,Duration) values ('1800134','{$ZhouYi}',{$rand_duration}) ";
$query = $mysqli->query($sql);
$sql = "INSERT into entry_ori_record (XueHao) values ('1800094') ";
$query = $mysqli->query($sql);
$sql = "INSERT into entry_ori_record (XueHao) values ('1800134') ";
$query = $mysqli->query($sql);
echo json_encode($callBack);
exit;
?>
